#!/usr/bin/perl
#
# This program adds a new account to the xibo-conference saas system
# The new account has its own database, html directory and library directory
#
# File layout
# This program will creates or manages two directories for each account.
# config variables, eg <account_web_base_dir> are set in the config.yaml file
# 1) In the dir <account_web_base_dir>
#    This directory must be visible via your http server.
#    urls for this directory start with <account_web_base_dir_url>
# 
#    This directory will be populated with links to the server sw
#    located at <xibo_server>
#
# 2) A directory will be created in directory <account_lib_base_dir> to
#    hold the library files for the account. These directories should
#    not be visible on the internet. To help insure this, .htaccess will be
#    copied from the xibo-confs/scripts dir into the new library dir for 
#    the account
#
# NB. Both directories must be readable and writable by your web
#     server. You should run this file as an appropriate user 
#     with permissions to create the directories appropriately.
#
# If configured in the config.yaml, the program will also create a 
# row of information in a table about the new account.


# The program has a manual, try  manage_accounts -man   
#
#
# INSTALL TIPS
# cpan install String::MkPasswd
#      install WWW::Mechanize
#      install File::Path
#
use strict;
use warnings;
use DBD::mysql;
use Getopt::Long; # See http://stackoverflow.com/questions/361752/how-can-i-pass-command-line-arguments-to-a-perl-program
  # and http://perldoc.perl.org/Getopt/Long.html
use Pod::Usage;
use File::Spec::Functions qw(rel2abs);
use File::Basename;
use YAML;
use File::Copy;
use File::Path qw(remove_tree);
use String::MkPasswd qw(mkpasswd);
use WWW::Mechanize;
use Data::Dumper;

# Settings
my $current_dir = dirname(rel2abs($0));
my $config = get_yaml_settings();

my $db_server = $config->{db_server};
my $db_root_name = $config->{db_root_name};
my $db_root_pw = $config->{db_root_pw};
my $xibo_server_dir = dir_expand($config->{xibo_server_dir});
my $account_web_base_dir = dir_expand($config->{account_web_dir});
my $account_lib_base_dir = dir_expand($config->{account_lib_dir});
my $account_web_base_dir_url = $config->{account_web_dir_url};
my $accounts_info_db = $config->{accounts_info_db};
my $accounts_info_table = $config->{accounts_info_table};
my $xibo_admin = $config->{xibo_admin};
my $ppt_ok = $config->{ppt_ok};
my $stats_ok = $config->{stats_ok};
my $schedule_ok = $config->{schedule_ok};

# arguments...
my $man = 0;
my $help = 0;
my $conf_id = 0;
my $account_name = 0;
my $op = 'add';

# globals used for adding an account.
my $admin_pw = '';     # pw for the account's 'admin' user. NB exact user name is set in 
                       # saas_settings.php, var: $xibo_admin. Default is 'xibo_admin'
my $server_name = '';  # The secret server name used by display clients to reach the server
my $account_db_name = '';       # database name in the db
my $account_db_user_name = '';  # user name in the db
my $account_db_pw = '';         # pw for user in the db
my $accounts_info_id = 0; # which entry in the accounts_info_table?
my $account_web_dir = '';
my $account_lib_dir = '';
my $account_web_url = '';
my $account_xibo_ver = '';
my $email = ''; # email of primary account person

GetOptions(          'help|?' => \$help,
                          man => \$man,
                  'conf_id=i' => \$conf_id, # numeric
                       'op=s' => \$op, # string
             'account_name=s' => \$account_name, # string
                 'admin_pw=s' => \$admin_pw, # string
              'server_name=s' => \$server_name, # string
                    'email=s' => \$email, # string
           ) or pod2usage(2);
pod2usage(-exitstatus => 1, -verbose => 2) if $man;
$help = $help ||
   !(
      ($op eq 'add' && $admin_pw && $server_name && ($account_name || $conf_id)) || 
      ($op eq 'upgrade' && ($account_name || $conf_id)) ||
      ($op eq 'rm'  && ($account_name || $conf_id)) ||
      ($op eq 'ver' && ($account_name || $conf_id)) ||
      ($op eq 'configure') ||
      ($op eq 'list')
    );
pod2usage(1) if $help;

# sanity checks
($account_web_base_dir ne $account_lib_base_dir) or 
  report_problem ("Account web and lib directories are same! They must be different.");
(-d $xibo_server_dir) or
  report_problem ("$xibo_server_dir does not exist!");
(-d $account_web_base_dir) or
  report_problem ("$account_web_base_dir does not exist!");
(-d $account_lib_base_dir) or
  report_problem ("$account_lib_base_dir does not exist!");

# database constants --
# Connect to the accounts_info_db or the mysql database 
my $db_name = ($accounts_info_db && $op ne 'configure') ? $accounts_info_db : 'mysql'; 
my $dsn="DBI:mysql:$db_name:$db_server";

# db login
my $dbh = DBI->connect($dsn, $db_root_name, $db_root_pw,
  { RaiseError => 0, AutoCommit => 1 });
if (! $dbh)
  {report_problem ("Could not open database connection.")};
  

########################################################################
########################################################################

# And awaaay we go!

$account_name = lc ($account_name); # Be defensive, change to lowercase
$account_name =~ s/[^a-z0-9\-\_]/-/g; # only allow letters, numbers, dash
if ($account_name !~ /^[a-z](.)*/) {$account_name = "x-$account_name"};
# set up account-specific vars
new_account_name();

my $results;
if    ($op eq 'add') {$results = add()}
elsif ($op eq 'rm' ) {$results = rm()}
elsif ($op eq 'upgrade' ) {$results = upgrade()}
elsif ($op eq 'ver') {$results = ver()}
elsif ($op eq 'configure') {$results = configure_accounts_table()}
elsif ($op eq 'list') {$results = list()}
else  {report_problem ("unknown op: $op")};

print $results, "\n";
exit(0);

# end of mainline. Methods....

########################################################################### 
########################################################################### 
#
#    new_account_name
#
########################################################################### 
########################################################################### 
sub new_account_name {
  $account_web_dir = "$account_web_base_dir/$account_name";
  $account_lib_dir = "$account_lib_base_dir/$account_name";
  $account_web_url = "$account_web_base_dir_url/$account_name";
}

########################################################################### 
########################################################################### 
#
#    add an account
#
########################################################################### 
########################################################################### 
sub add {
  my ($err);
    
  get_account_db_info();  

  # Step 0 handle prior account situations
  # ensure the account doesn't already exist
  if (-d $account_web_dir)
    {report_problem ("Account web directory already exists! Name: $account_name, Dir: $account_web_dir")};
  if (-d $account_lib_dir)
    {report_problem ("Account lib directory already exists! Name: $account_name, Dir: $account_lib_dir")};
  get_accounts_info_id();
  if ($accounts_info_id) {
    report_problem ("DB entry already exists! Conf id: $conf_id, db_id: $accounts_info_id")};
  
  create_accounts_info();
  
  # Step 1, create directories
  mkdir($account_web_dir) or
    report_problem ("Couldn't create account web directory: $account_web_dir");
  mkdir($account_lib_dir) or
    report_problem ("Couldn't create account lib directory: $account_lib_dir");
  update_all_accounts_info('created directories', '');
  
  # Step 2, create links and files in web directory
  create_links();
  
  # Step 3, create database
  $account_db_pw = make_pw();  
  my ($sql, $q);
  $sql = "create database $account_db_name";
  if (!($q = $dbh->do($sql))) {report_problem ("couldn't create db $account_db_name")};  
  $sql = "GRANT ALL PRIVILEGES ON $account_db_name.* TO ${account_db_user_name}\@localhost IDENTIFIED BY '$account_db_pw' WITH GRANT OPTION";
  if (!($q = $dbh->do($sql))) {report_problem ("couldn't create db user $account_name")};
  update_all_accounts_info('created db', '');
  
  # Step 4, use xibo install system
  xibo_install();
  
  $account_xibo_ver = get_xibo_version($account_web_url);
  update_all_accounts_info('installed', '');
  
  # Since we made it here, good results!
  return(results_from_add(1, ""));
};

########################################################################### 
########################################################################### 
#
#    upgrade
#
########################################################################### 
########################################################################### 
sub upgrade {
  # upgrade from a prior version
  my ($err);
    
  get_account_db_info();  

  # Ensure the account doesn't already exist
  if (!(-d $account_web_dir))
    {report_problem ("Account web directory doesn't exist! Name: $account_name, Dir: $account_web_dir")};
  if (!(-d $account_lib_dir))
    {report_problem ("Account lib directory doesn't exist! Name: $account_name, Dir: $account_lib_dir")};
  get_accounts_info_id();
  if (!$accounts_info_id) {
    report_problem ("DB entry doesn't exist! Conf id: $conf_id")};
    
  # step 0
  # Check account_name
  if (!$account_name) {
    get_account_name();
    new_account_name();
  }
  
  # Step 1, rm links from web directory
  remove_links();
  update_all_accounts_info('removed links', '');
  
  # Step 2, create web directory, links and files
  create_links();
  update_all_accounts_info('added links', '');
    
  # Step 3, use xibo upgrade system
  xibo_upgrade();
  
  # munge db
  munge_xibo_db();
  
  $account_xibo_ver = get_xibo_version($account_web_url);
  update_all_accounts_info('upgraded', '');
  
  # Since we made it here, good results!
  return(results_from_upgrade(1, ""));
};

sub remove_links {
  # remove all links from the directory $account_web_dir
  my @files = glob "$account_web_dir/*";
  
  foreach my $file (@files) {
    unlink($file) if (-l $file);
  }
}

########################################################################### 
########################################################################### 
#
#    create_links
#
########################################################################### 
########################################################################### 
sub create_links {
  # create links and files in web directory
  my @links = qw(3rdparty config img index.php install lib LICENSE.txt modules README.TXT
                 template xmds.php saas_settings);
  my @copies = qw(install.php upgrade.php);
  foreach my $link (@links) {
    unless (symlink("$xibo_server_dir/$link", "$account_web_dir/$link"))
      {report_problem ("Couldn't create link: $account_web_dir/$link")};
  } 
  foreach my $file (@copies) {
    unless (copy("$xibo_server_dir/$file", "$account_web_dir/$file"))
      {report_problem ("Couldn't create copy: $account_web_dir/$file")};
  } 
  # cp the .htaccess file
  copy("$current_dir/.htaccess", "$account_lib_dir/.htaccess");
  update_all_accounts_info('filled dirs', '');
};

########################################################################### 
########################################################################### 
#
#    results_from_add
#
########################################################################### 
########################################################################### 
sub results_from_add {
  # Creates results string for add operation
  # params: ok?, msg
  my($ok, $msg) = @_;
  
  $ok = $ok ? 'ok' : 'err';
  return("$ok|add|$account_name|$xibo_admin|$admin_pw|$server_name|$account_web_url|$msg");
};

########################################################################### 
########################################################################### 
#
#    results_from_upgrade
#
########################################################################### 
########################################################################### 
sub results_from_upgrade {
  # Creates results string for upgrade operation
  # params: ok?, msg
  my($ok, $msg) = @_;
  
  $ok = $ok ? 'ok' : 'err';
  return("$ok|upgrade|$account_name|$xibo_admin|$admin_pw|$server_name|$account_web_url|$msg");
};

########################################################################### 
########################################################################### 
#
#    configure_accounts_table
#
########################################################################### 
########################################################################### 
sub configure_accounts_table {
  # Creates accounts db and table
  
  if (!$accounts_info_db || !$accounts_info_table) {return};
    
  my ($sql, $q);
  $sql = "create database if not exists $accounts_info_db";
  if (!($q = $dbh->do($sql))) {report_problem ("couldn't create db $accounts_info_db")};
   
  $sql = "Use $accounts_info_db";
  if (!($q = $dbh->do($sql))) {report_problem ("couldn't switch to $accounts_info_db")};

  $sql = "Create table if not exists $accounts_info_table (
          id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
          conference_id INT,
          email VARCHAR(255),
          account_name VARCHAR(255),
          xibo_ver VARCHAR(255),
          account_web_url VARCHAR(255),
          admin_pw VARCHAR(255),
          server_name VARCHAR(255),
          account_db_name VARCHAR(255),
          account_db_user_name VARCHAR(255),
          account_db_pw VARCHAR(255),
          status VARCHAR(255),
          msg TEXT
        )";
  if (!($q = $dbh->do($sql))) {report_problem ("couldn't create db table $accounts_info_table")};

  $sql = "CREATE UNIQUE INDEX ai_index1 ON $accounts_info_table(conference_id)";
  if (!($q = $dbh->do($sql))) {report_problem ("couldn't create db table $accounts_info_table")};
  
  $sql = "CREATE INDEX ai_index2 ON $accounts_info_table(status)";
  if (!($q = $dbh->do($sql))) {report_problem ("couldn't create db table $accounts_info_table")};

  $sql = "CREATE UNIQUE INDEX ai_index3 ON $accounts_info_table(account_name)";
  if (!($q = $dbh->do($sql))) {report_problem ("couldn't create db table $accounts_info_table")};
  
  print "ok|configure_accounts_table";
};

########################################################################### 
########################################################################### 
#
#    list
#
########################################################################### 
########################################################################### 
sub list {
  # list table from db
  if (!$accounts_info_db || !$accounts_info_table) {
    print "Problem: missing settings for database table\n";
    return};
  
  my $sql = "Use $accounts_info_db";
  if (!($dbh->do($sql))) {report_problem ("couldn't switch to $accounts_info_db")};

  my $qhandle = $dbh -> prepare("select * from $accounts_info_table");
  $qhandle -> execute();
  
  my ($results, @fig, $n, $fname);
  
  while ($results = $qhandle->fetchrow_hashref()) {
    @fig = sort(keys(%$results));
    if (! $n++) {
            print (join(" --- ",@fig),"\n");
    }
    foreach $fname (@fig) {
            print ($results->{$fname}," ...  ");
    }
    print "\n";
  }
  return('');
}

########################################################################### 
########################################################################### 
#
#    create_accounts_info
#
########################################################################### 
########################################################################### 
sub create_accounts_info {
  # create entry in db
  if (!$accounts_info_db || !$accounts_info_table) {return};
  
  my $sql = "INSERT INTO $accounts_info_table 
     (conference_id,
      email,
      account_name,
      xibo_ver,
      account_web_url,
      admin_pw,
      server_name,
      account_db_name,
      account_db_user_name,
      account_db_pw,
      status,
      msg 
     ) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
  my $sth = $dbh->prepare($sql);
  
  $sth->execute(
                   $conf_id,
                     $email,
              $account_name,
          $account_xibo_ver,
           $account_web_url,
                  $admin_pw,
               $server_name,
           $account_db_name,
      $account_db_user_name,
             $account_db_pw,
                     "init", # status
                      "init" # msg
                   );
                   
  $accounts_info_id = $dbh->{q{mysql_insertid}}; # See http://paulm.com/inchoate/2005/07/mysql_perl_dbi_last_insert_id.html
};

########################################################################### 
########################################################################### 
#
#    update_all_accounts_info
#
########################################################################### 
########################################################################### 
sub update_all_accounts_info {
  # updates all items. 
  # params: status, msg
  my $status = shift;
  my $msg = shift;
  $status = substr($status,0,255);
  $msg = substr($msg,0,255);
  
  if (!$accounts_info_db || !$accounts_info_table) {return};
  
  my $sql = "UPDATE $accounts_info_table 
     set conference_id = ?,
         email = ?,
         account_name = ?,
         xibo_ver = ?,
         account_web_url = ?,
         admin_pw = ?,
         server_name = ?,
         account_db_name = ?,
         account_db_user_name = ?,
         account_db_pw = ?,
         status = ?,
         msg  = ?
     WHERE id = $accounts_info_id";

  my $sth = $dbh->prepare($sql);
  
  $sth->execute(
                   $conf_id,
                     $email,
              $account_name,
          $account_xibo_ver,
           $account_web_url,
                  $admin_pw,
               $server_name,
           $account_db_name,
      $account_db_user_name,
             $account_db_pw,
                    $status,
                       $msg
               );
};


########################################################################### 
########################################################################### 
#
#    update_some_accounts_info
#
########################################################################### 
########################################################################### 
sub update_some_accounts_info {
  # updates status and msg. 
  # params: status, msg
  my $status = shift;
  my $msg = shift;
  $status = substr($status,0,255);
  $msg = substr($msg,0,255);
  
  if (!$accounts_info_db && !$accounts_info_table) {return};
  
  my $sql = "UPDATE $accounts_info_table 
     set status = ?,
         msg = ?
     WHERE id = $accounts_info_id";
  my $sth = $dbh->prepare($sql);
  
  $sth->execute(
         $status,
            $msg
               );
};

########################################################################### 
########################################################################### 
#
#    make_pw
#
########################################################################### 
########################################################################### 
sub make_pw {
  my $pw = mkpasswd(); # see http://articles.techrepublic.com.com/5100-10878_11-5890878.html
  $pw =~ s/[^a-zA-Z0-9\-\_\!\$]/\!/g; # only allow some characters  
  return($pw);
};


########################################################################### 
########################################################################### 
#
#    dir_expand
#
########################################################################### 
########################################################################### 
sub dir_expand {
  # expands possible tildes in dir name
  # param: dir -- a directory name
  my $dir = shift;
  
  chop($dir = `echo $dir`); # expand tildes

  return($dir);
};

########################################################################### 
########################################################################### 
#
#    get_account_db_info
#
########################################################################### 
########################################################################### 
sub get_account_db_info {
  # Set account_db_name  and $account_db_user_name
  
  $account_db_name = "x_${account_name}_$conf_id";
  $account_db_name =~ s/\-/_/g; # db names can't have dashes.
  $account_db_user_name = $account_db_name;
};

########################################################################### 
########################################################################### 
#
#    get_account_name
#
########################################################################### 
########################################################################### 
sub get_account_name {
  # Get account_name by looking up conf_id 
  
  if ($accounts_info_db && $accounts_info_table) {
    my $sql = "Select account_name from $accounts_info_table Where conference_id = ?";
    my $sth = $dbh->prepare($sql);
    $sth->execute($conf_id);
    $account_name = $sth->fetchrow_array;    
  };
  return($account_name);
};

########################################################################### 
########################################################################### 
#
#    get_accounts_info_id
#
########################################################################### 
########################################################################### 
sub get_accounts_info_id {
  # Get accounts_info_id by looking up conf_id 
  
  if ($accounts_info_db && $accounts_info_table) {
    my $sql = "Select id from $accounts_info_table Where conference_id = ?";
    my $sth = $dbh->prepare($sql);
    $sth->execute($conf_id);
    $accounts_info_id = $sth->fetchrow_array;    
  };
};

########################################################################### 
########################################################################### 
#
#    rm an account
#
########################################################################### 
########################################################################### 
sub rm {
  # remove an existing account via conf_id or account_name
  
  if ($accounts_info_db && $accounts_info_table) {
    my ($t_account_db_name, $t_account_name);
    my $sql = "Select id, account_name, account_db_name from $accounts_info_table
               Where conference_id = ? or account_name = ?";
    my $sth = $dbh->prepare($sql);
    $sth->execute($conf_id, $account_name);
    ($accounts_info_id, $t_account_name, $t_account_db_name) = $sth->fetchrow_array;
    
    rm_directories($t_account_name);
    rm_db($t_account_db_name);
    rm_accounts_info_rec();
  };
  
  rm_db($account_db_name);
  rm_directories($account_name);
  
  return("ok|rm|$conf_id|$account_name");
};

sub rm_directories {
  my $t_account_name = shift;
  if (!defined $t_account_name) {return};
  remove_tree("$account_web_base_dir/$t_account_name");
  remove_tree("$account_lib_base_dir/$t_account_name");
};

sub rm_db {
  my $t_account_db_name = shift;
  if (!defined $t_account_db_name || !$t_account_db_name) {return};  
  my $sql = "drop database if exists $t_account_db_name";
  $dbh->do($sql);
}

sub rm_accounts_info_rec {
  # uses $accounts_info_id;
  if (!$accounts_info_db || !$accounts_info_table) {return};
  if (!defined $accounts_info_id || !$accounts_info_id) {return};
  my $sql = "delete from $accounts_info_table where id = $accounts_info_id";
  $dbh->do($sql);
};

########################################################################### 
########################################################################### 
#
#    get an account version
#
########################################################################### 
########################################################################### 
sub ver {
  my $ver = get_xibo_version($account_web_url);
  return("ok|ver|$ver");
};


########################################################################### 
########################################################################### 
#
#    report_problem
#
########################################################################### 
########################################################################### 
sub report_problem {
  my ($err, $page) = @_;
  if (!(defined $page)) {$page = '';}
  
  if ($op eq 'add') {
    update_all_accounts_info("ERROR: $err", $page); 
    print results_from_add(0, $err);  
  } elsif ($op eq 'upgrade') {
    update_all_accounts_info("ERROR: $err", $page); 
    print results_from_upgrade(0, $err);  
  } else {
    print STDERR "ERROR: $err\n\n";
    if (defined $page) {
      print "\n\n", $page, "\n\n";}
  }

  exit(1);
};


########################################################################### 
########################################################################### 
#
#    get_yaml_settings
#
########################################################################### 
########################################################################### 
sub get_yaml_settings {
  # Settings from yaml, see http://www.perlmonks.org/?node_id=620076
  
  my $yaml_file = 'config.yaml';
  my $filename = "$current_dir/config.yaml";

  open my $fh, '<', $filename 
    or report_problem("can't open config file: $!");

  my $yml = do { local $/; <$fh> };

  # convert YAML 'stream' to perl hash ref
  return(Load($yml));
};

########################################################################### 
########################################################################### 
#
#    get_xibo_version
#
########################################################################### 
########################################################################### 
sub get_xibo_version {
  # probe the web site to get the version info
  my $url = shift; # param: url
  
  my $agent = WWW::Mechanize->new();
  $agent->get($url);
  
  # Version 1.0.5 |
  my $matched = ($agent->content() =~ /Version ([^w]*) \|/);
  return($matched ? $1 : '');
}


########################################################################### 
########################################################################### 
#
#    xibo_install
#
########################################################################### 
########################################################################### 
sub xibo_install {
  # Uses WWW::Mechanize to run the installation script  

  my $t;
  my $agent = WWW::Mechanize->new();
  $agent->get($account_web_url);
  
  # page 1. check that it is the page we expect
  update_all_accounts_info('received pg 1', '');
  ($agent->content() =~ /The installer will take you through setting up/)
    or report_problem("no install page at $account_web_url", $agent->content());
  $agent->form_number(1); $agent->click(); # click Next
  
  # page 2.
  update_all_accounts_info('received pg 2', '');
  ($agent->content() =~ /First we need to check if your server meets/)
    or report_problem("page 2 not right at $account_web_url", $agent->content());
  $agent->form_number(1); $agent->click(); # click Next

  # page 3.
  update_all_accounts_info('received pg 3', '');
  ($agent->content() =~ /needs to setup a new database./)
    or report_problem("page 3 not right at $account_web_url", $agent->content());  
  # form 2, with button "Use Existing", is xibo_step 4. Confirm:
  $agent->form_number(2);
  ($agent->value('xibo_step') == 4)
    or report_problem("page 3 form 2 not right. xibo_step != 4 at $account_web_url", $agent->content());
  $agent->click();
  
  # page 4
  update_all_accounts_info('received pg 4', '');
  ($agent->content() =~ /Please enter the details of the database and user you have/, $agent->content())
    or report_problem("page 4 not right at $account_web_url");
  $agent->form_number(1);
  ($agent->value('xibo_step') == 5)
    or report_problem("page 4 form not right. xibo_step != 5 at $account_web_url", $agent->content());
  # fill in form...
  $agent->field("host", $db_server);
  $agent->field("db_name", $account_db_name);
  $agent->field("db_username", $account_db_user_name);
  $agent->field("db_password", $account_db_pw);
  $agent->click();
  
  # page 5
  update_all_accounts_info('received pg 5', '');
  ($agent->content() =~ /Populating the database/)
    or report_problem("page 5 not right at $account_web_url", $agent->content());
  $agent->form_number(1);
  $agent->click();

  # Database populated. Now munge it
  munge_xibo_db();
  
  # page 6
  update_all_accounts_info('received pg 6', '');
  ($agent->content() =~ /Please enter a password for this account below/)
    or report_problem("page 6 not right at $account_web_url", $agent->content());
  $agent->form_number(1);
  ($agent->value('xibo_step') == 7)
    or report_problem("page 6 form not right. xibo_step != 7 at $account_web_url", $agent->content());
  # fill in form...
  $agent->field("password1", $admin_pw);
  $agent->field("password2", $admin_pw);
  $agent->click();

  # page 7
  update_all_accounts_info('received pg 7', '');
  ($agent->content() =~ /Successfully changed the/)
    or report_problem("page 7 not right at $account_web_url", $agent->content());
  $agent->form_number(1);
  $agent->click();
    
  # page 8
  update_all_accounts_info('received pg 8', '');
  ($agent->content() =~ /Library Location/)
    or report_problem("page 8 not right at $account_web_url", $agent->content());
  $agent->form_number(1);
  ($agent->value('xibo_step') == 9)
    or report_problem("page 8 form not right. xibo_step != 9 at $account_web_url", $agent->content());
  # fill in form...
  $agent->field("library_location", $account_lib_dir);
  $agent->field("server_key", $server_name);
  if ($agent->content() =~ /stats/) {
    if ($stats_ok) {$agent->tick("stats", "true")} else
                   {$agent->untick("stats", "true")};
  };
  $agent->click();

  # page 9
  update_all_accounts_info('received pg 9', '');
  ($agent->content() =~ /Successfully set LIBRARY_LOCATION and SERVER_KEY/)
    or report_problem("page 9 not right at $account_web_url", $agent->content());
  $agent->form_number(1);
  $agent->click();

  # page 10
  update_all_accounts_info('installed', '');
  ($agent->content() =~ /was successfully installed/)
    or report_problem("page 10 not right at $account_web_url", $agent->content());
  
  #print Dumper($t), "\n\n";  
};


########################################################################### 
########################################################################### 
#
#    xibo_upgrade
#
########################################################################### 
########################################################################### 
sub xibo_upgrade {
  # Uses WWW::Mechanize to run the upgrade script  

  my $t;
  my $agent = WWW::Mechanize->new();
  $agent->get($account_web_url);
  
  # page 1. check that it is the page we expect
  update_all_accounts_info('received pg 1', '');
  ($agent->content() =~ /The upgrade program will take you through the process/)
    or report_problem("no upgrade page at $account_web_url", $agent->content());
  $agent->form_number(1);
  $agent->field("password", $admin_pw);
  $agent->click(); # click Next
  
  # page 2.
  update_all_accounts_info('received pg 2', '');
  ($agent->content() =~ /First we need to check if your server meets/)
    or report_problem("page 2 not right at $account_web_url", $agent->content());
  $agent->form_number(1); $agent->click(); # click Next

  # page 3.
  update_all_accounts_info('received pg 3', '');
  ($agent->content() =~ /Upgrading from database version/)
    or report_problem("page 3 not right at $account_web_url", $agent->content());  
  $agent->form_number(1); $agent->click(); # click Next
  
  # page 4
  update_all_accounts_info('received pg 4', '');
  ($agent->content() =~ /Upgrade is complete\!/, $agent->content())
    or report_problem("page 4 not right at $account_web_url");
};

########################################################################### 
########################################################################### 
#
#    munge_xibo_db
#
########################################################################### 
########################################################################### 
sub munge_xibo_db {
  # Make various changes to set up the database to better suit a saas environment
  # Settings controlled in config.yaml
  # Connect to the Xibo database
  my $q;
  my $sql = "Use $account_db_name";
  if (!($q = $dbh->do($sql))) {report_problem ("couldn't switch to $account_db_name")};
  
  # Change xibo_admin database entry
  # Here is the default entry from 1.sql--
  # INSERT INTO `user` (`UserID`, `usertypeid`, `UserName`, `UserPassword`, `loggedin`, `lastaccessed`, `email`, `groupID`, `homepage`) VALUES
  # (1, 1, 'xibo_admin', '21232f297a57a5a743894a0e4a801fc3', 0, '2008-04-29 09:34:43', 'info@xibo.org.uk', 1, 'dashboard');
  # Update to fix the user_name and email
  $sql = "Update user Set UserName = ?, email = ? Where UserID = 1";
  my $sth = $dbh->prepare($sql);
  $sth->execute($xibo_admin, $email);
  
  # Remove Powerpoint from module table
  # From 1.sql --
  # INSERT INTO `module` (`ModuleID`, `Module`, `Enabled`, `RegionSpecific`, `Description`, `ImageUri`, `SchemaVersion`) VALUES
  # (4, 'Powerpoint', 1, 0, 'Powerpoint. PPT, PPS', 'img/forms/powerpoint.gif', 1),
  if (!$ppt_ok) {
    $sql = "Delete from module where Module = 'Powerpoint'";
    $dbh->do($sql);
  };
  
  # Perhaps remove Scheduling screens from system
  if (!$schedule_ok) {
    # need to remove leafs, then branches
    $sql = "Delete lkmenuitemgroup
            From lkmenuitemgroup, menuitem
            Where menuitem.MenuItemID = lkmenuitemgroup.MenuItemID AND
            menuitem.Text = 'Schedule'";
    $dbh->do($sql);
    
    $sql = "Delete from menuitem where Text = 'Schedule'";
    $dbh->do($sql);
  };
  
  # Remove fault reporting menu item. You can still use url
  #  ... /index.php?p=fault& 
  # orig: INSERT INTO `menuitem` (`MenuItemID`, `MenuID`, `PageID`, `Args`, `Text`, `Class`, `Img`, `Sequence`) 
  #                        VALUES (NULL, '4', '26', NULL, 'Report Fault', NULL, NULL, '8');
  $sql = "Delete from menuitem where Text = 'Report Fault'";  
  $dbh->do($sql);                                         

  # Reset sql connection to use accounts_info db
  $sql = "Use $accounts_info_db";
  if (!($q = $dbh->do($sql))) {report_problem ("couldn't switch to $accounts_info_db")};

};




########################################################################### 
########################################################################### 



__END__

=head1 NAME

manage_accounts - Creates or manages an Xibo-Conference user account

=head1 SYNOPSIS

B<Adding an account:>
manage_accounts -conf_id 1234 -account_name dof123 -admin_pw secret! -server_name also_secret! [-email foo@bar.com]

B<Removing an account:>
manage_accounts -op rm [-account_name dof123] [-conf_id 123]

B<Find version of an existing account's sw:> manage_accounts -op ver -account_name dof123

B<configure meta db for account info:> manage_accounts -op configure

B<show manual:> manage_accounts -man

=head1 DESCRIPTION

B<manage_accounts> will manage accounts on the Xibo-Conference system.

B<Adding an account>
It creates appropriate web and library directories,
a database, and initializes the new account with Xibo-Conference

=head1 RETURN VALUE

manage_accounts returns one line that has multiple parts separate by pipe symbol -- |

B<add operation>- <ok or err>|add|updated_account_name|admin_name|admin_pw|server_name|url|msg

<ok or err> -- either 'ok' or 'err'

updated_account_name -- that was used. May be different from input depending on character issues.

admin_pw -- the pw for the new admin user.

server_name -- the server_name used by the client display systems.

url -- complete url that can be used for reaching the new account

msg -- a message about the success or failure of creating the account. Success messages do not have to be passed to the user.

=head1 Click  q  to close.

=cut
